package com.jxltech.unicam.utils;

import cn.hutool.core.util.IdUtil;

import java.sql.*;

public class DerbyDb {
	private static String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	private static String url = "jdbc:derby:dbfile/imgcheck;create=true";

	public static Connection conn;


	public static Connection getConnection() throws SQLException {
		if (conn == null || conn.isClosed()) {
			try {
				Class.forName(driver);
			} catch (Exception ex) {
				ex.printStackTrace();
			}

			conn = DriverManager.getConnection(url);
		}
		return conn;
	}

	public static void closeConnection() throws SQLException {
		if (conn == null && !conn.isClosed()) {
			conn.close();
			conn = null;
		}
	}

	public static void insertImg(String img, boolean checkResult) throws SQLException {
		try (Statement stmt = getConnection().createStatement()) {

			long id = IdUtil.getSnowflake(1, 1).nextId();
			stmt.execute(String.format("INSERT INTO T_IMG_INFO(ID, IMG_NAME, RESULT) VALUES (%d, '%s', %s)", id, img, Boolean.toString(checkResult)));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void queryImg() throws SQLException {
		try (Statement stmt = getConnection().createStatement()) {
			ResultSet rs = stmt.executeQuery("SELECT * FROM T_IMG_INFO");

			while (rs.next()) {
				System.out.println(rs.getLong(1) + "  " + rs.getString(2) + "  " + rs.getBoolean(3));
			}
			rs.close();
		}
	}

	public static SysConfig queryConfig() throws SQLException {
		SysConfig config = new SysConfig();
		try (Statement stmt = getConnection().createStatement()) {
			ResultSet rs = stmt.executeQuery("SELECT * FROM T_SYS_CONFIG");

			while (rs.next()) {
				config.setxVal(rs.getInt("x_val"));
				config.setyVal(rs.getInt("y_val"));
				config.setgVal(rs.getInt("g_val"));
				config.setpVal(rs.getString("p_val"));
			}
			rs.close();
		}
		return config;
	}

	public static void insertConifg() {
		try (Statement stmt = getConnection().createStatement()) {

			long id = IdUtil.getSnowflake(1, 1).nextId();
			stmt.execute(String.format("INSERT INTO T_SYS_CONFIG(ID, X_VAL, Y_VAL, G_VAL, P_VAL) VALUES (%d, 0,0,0,'')", id));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void updateConifg(SysConfig config) {
		try (Statement stmt = getConnection().createStatement()) {
			stmt.execute(String.format("UPDATE T_SYS_CONFIG SET x_val = %s, y_val = %s, g_val = %s, p_val = '%s' ",
					config.getxVal(),
					config.getyVal(),
					config.getgVal(),
					config.getpVal()));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void init() throws SQLException {
		//创建表 T_IMG_INFO
		String imgSql = "CREATE TABLE T_IMG_INFO (id BIGINT not NULL, img_name VARCHAR(255), result BOOLEAN, PRIMARY KEY ( id ))";
		createTable(imgSql, "T_IMG_INFO");
		//创建表 T_SYS_CONFIG
		String configSql = "CREATE TABLE T_SYS_CONFIG (id BIGINT not NULL, x_val INTEGER DEFAULT 0, y_val INTEGER DEFAULT 0, g_val INTEGER DEFAULT 0, p_val VARCHAR(100), PRIMARY KEY ( id ))";
		if (createTable(configSql, "T_SYS_CONFIG"))
			insertConifg();
		Constants.config = queryConfig();
	}

	public static boolean createTable(String createSql, String tableName) throws SQLException {
		Statement stmt = null;
		try {

			//STEP 3: Open a connection
			System.out.println("Connecting to a selected database...");
			Connection conn = getConnection();
			System.out.println("Connected database successfully...");

			//STEP 4: Execute a query
			System.out.println("Creating table in given database...");
			stmt = conn.createStatement();
			stmt.executeUpdate(createSql);
			System.out.println(String.format("Created table %s in given database...", tableName));
			return true;
		} catch (SQLException se) {
			//Handle errors for JDBC
			if (se.getMessage().contains("already exists")) {
				System.out.println(String.format("%s already exists", tableName));
			} else {
				se.printStackTrace();
			}
			return false;
		} finally {
			if (stmt != null) {
				stmt.close();
			}
			System.out.println("Done");
		}
	}

	public static void main(String[] s) throws SQLException {
		init();
//		insertImg("dddd", true);
//		insertImg("ssss", false);
		queryImg();
		SysConfig config = queryConfig();
		config.setgVal(5);
		updateConifg(config);
		config = queryConfig();

	}
}
